package com.anolesoft.yr.mbus.prod.base.sql;

public class ProdBaseServiceSQL {

    //新增时查询服务项基础编是否已经存在
    public static final String QUERY_PROD_BASE_SERVICE_BY_CODE = "select count(*) as count from prod_base_service a where a.del_flag=0 and a.base_service_code=#{baseServiceCode}";

    //新增时查询服务项基础名称是否已经存在
    public static final String QUERY_PROD_BASE_SERVICE_BY_NAME = "select count(*) as count from prod_base_service a where a.del_flag=0 and a.base_service_name=#{baseServiceName}";

    //新增服务项基础信息表
    public static final String SAVE_PROD_BASE_SERVICE = "insert into prod_base_service(objuid,fac_code,base_service_no,base_service_code,base_service_name,import_type,is_timeliness,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{baseServiceNo},#{baseServiceCode},#{baseServiceName},#{importType},#{isTimeliness},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //根据主键查询服务项基础唯一数据
    public static final String QUERY_PROD_BASE_SERVICE_BY_UID = "select a.objuid,a.base_service_code,a.base_service_name,a.import_type,case when a.is_timeliness=0 then '否' else '是' end is_timeliness,a.remark,a.c_user_uid,a.c_time from prod_base_service a where a.del_flag=0 and a.objuid=#{uid}";

    //根据主键查询服务项基础唯一数据
    public static final String QUERY_PROD_BASE_SERVICE_BY_UID2 = "select a.* from prod_base_service a where a.del_flag=0 and a.objuid=#{uid}";

    //修改时查询服务项基础编是否已经存在（不包含本身）
    public static final String QUERY2_PROD_BASE_SERVICE_BY_CODE = "select count(*) as count from prod_base_service a where a.del_flag=0 and a.base_service_code=#{baseServiceCode} and a.base_service_code<>#{baseServiceCode2}";

    //修改时查询服务项基础名称是否已经存在（不包含本身）
    public static final String QUERY2_PROD_BASE_SERVICE_BY_NAME = "select count(*) as count from prod_base_service a where a.del_flag=0 and a.base_service_name=#{baseServiceName} and a.base_service_name<>#{baseServiceName2}";

    //修改服务项基础信息表
    public static final String UPDATE_PROD_BASE_SERVICE = "update prod_base_service a set a.fac_code=#{facCode},a.base_service_no=#{baseServiceNo},a.base_service_code=#{baseServiceCode},a.base_service_name=#{baseServiceName},a.import_type=#{importType},a.is_timeliness=#{isTimeliness},a.remark=#{remark},a.m_user_uid=#{mmUserUid},a.m_time=#{mmTime} where a.objuid=#{objuid}";

    //删除服务项基础信息表
    public static final String DELETE_PROD_BASE_SERVICE = "update prod_base_service a set a.del_flag=1 where a.objuid=#{objuid}";

    //根据条件查询服务项基础数据条数
    public static final String QUERY_PROD_BASE_SERVICE_ALL_COUNT = "<script> select count(*) as count from prod_base_service a where a.del_flag=0 <if test=\"baseServiceName!=''\"> and upper(a.base_service_name) like upper(concat('%',#{baseServiceName},'%')) </if> </script>";

    //根据条件查询服务项基础所有数据
    public static final String QUERY_PROD_BASE_SERVICE_ALL = "<script> select a.objuid,a.base_service_code,a.base_service_name,a.import_type,case when a.is_timeliness=0 then '否' else '是' end is_timeliness,a.remark,b.emp_name as c_user_uid,a.c_time from prod_base_service a left join fac_employee b on b.objuid=a.c_user_uid where a.del_flag=0 <if test=\"baseServiceName!=''\"> and upper(a.base_service_name) like upper(concat('%',#{baseServiceName},'%')) </if> order by a.base_service_name asc limit #{page},#{limit} </script>";

    //查询基础服务项名称
    public static final String QUERY_PROD_BASE_SERVICE_NAME = "select a.objuid, a.base_service_name from prod_base_service a where a.del_flag=0 order by a.base_service_name asc";

}